<?php

class ChangeLogTools{
	/**
	 * 根據 Image ID 找到對應的 Episode IDs & Programme ID
	 * 查找方法為： Image ID -> Folder Item -> Programme -> Episode
	 * @return array('programme_id'=>$id, 'episode_id'=>array($id,$id..)
	 */
	static function getEpisodeIdsByImageId($image_id){
		$ret = array('programme_id'=>0,'episode_id'=>array());
		$dbh = Model::$dbo['w']->getDbHandle();
		$sql = 'select group_concat(folder_id) ids from folder_item fi inner join folder f';
		$sql.= ' on fi.folder_id=f.id and f.folder_item_type_id=? where item_id=?';
		$rs  = CommonUtils::exec_sql($dbh,$sql,array(AC::FOLDER_TYPE_IMAGE,$image_id));
		if(!empty($rs[0]['ids'])){
			$folders  = explode(',',$rs[0]['ids']);
			$total_fs = count($folders);
			$sql2 = 'select p.folders pfs,p.id pid,e.folders efs,e.id eid,c.folders cfs,c.id cid from image i inner join programme p';
			$sql2.= ' on i.programme_id=p.id left join episode e ';
			$sql2.= ' on e.programme_id=p.id left join cast c ';
			$sql2.= ' on c.programme_id=p.id where i.id=?';
			$rs2  = CommonUtils::exec_sql($dbh,$sql2,array($image_id));
			if($rs2){
				$in_casts = array();

				$p_folders = explode(',',$rs2[0]['pfs']);
				if(count(array_diff($folders,$p_folders)) != $total_fs){
					$ret['programme_id'] = $rs2[0]['pid'];
				}

				$cache = array();
				foreach($rs2 as $row){
					$e_cache_key = 'e'.$row['eid'];
					$c_cache_key = 'c'.$row['cid'];

					if($row['efs'] && !isset($cache[$e_cache_key])){
						$e_folders = explode(',',$row['efs']);
						if(count(array_diff($folders,$e_folders)) != $total_fs){
							$ret['episode_id'][] = $row['eid'];
						}
						$cache[$e_cache_key] = true;
					}

					if($row['cfs'] && !isset($cache[$c_cache_key])){
						$c_folders = explode(',',$row['cfs']);
						if(count(array_diff($folders,$c_folders)) != $total_fs){
							$in_casts[] = $row['cid'];
						}
						$cache[$c_cache_key] = true;
					}
				}
				unset($cache);

				foreach($in_casts as $cast_id){
					$data = self::getEpisodeIdsByCastId($cast_id);
					if($data['programme_id']){
						$ret['programme_id'] = $data['programme_id'];
					}
					$ret['episode_id'] = array_merge($ret['episode_id'], $data['episode_id']);
				}
				$ret['episode_id'] = array_unique($ret['episode_id']);
			}
		}
		$ret['episode_id'] = array_values($ret['episode_id']);
		return $ret;
	}

	/**
	 * 根據 Cast ID 找到對應的 Episode IDs & Programme ID
	 * 查找方法為： Cast ID -> Programme ID -> Episode IDs
	 * @return array('programme_id'=>$id, 'episode_id'=>array($id,$id..)
	 */
	static function getEpisodeIdsByCastId($cast_id){
		$ret = array('programme_id'=>0,'episode_id'=>array());
		$dbh = Model::$dbo['w']->getDbHandle();
		$sql = 'select p.casts p_casts,p.id as pid,ifnull(e.id,0) as eid,ifnull(e.casts,"") e_casts from cast c inner join programme p on c.programme_id=p.id ';
		$sql.= 'left join episode e on p.id=e.programme_id where c.id=?';
		$rs  = CommonUtils::exec_sql($dbh,$sql,array($cast_id));
		if($rs){
			$ret['programme_id'] = CommonUtils::existsCastInJson($rs[0]['p_casts'],$cast_id) ? $rs[0]['pid'] : 0;
			foreach($rs as $row){
				if(CommonUtils::existsCastInJson($row['e_casts'],$cast_id)){
					$ret['episode_id'][] = $row['eid'];
				}
			}
		}
		$ret['episode_id'] = array_values($ret['episode_id']);
		return $ret;
	}
}


